﻿using Maticsoft.DAL;
using StudentOP.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace StudentOP.Controllers
{
    public class StudentController : Controller
    {
        // GET: Student
        public ActionResult List()
        {
            //连接数据库，查询数据，返回一个list集合，最后传递给view
            //string sql = "select t.Id,t.Name,t.Age, t.Mobile,t.Email,c.Id as classID ,c.ClassName from  Student t inner join Class c on t.ClassId=c.Id ";
            //DataTable tb = SqlHelper.MyDataTable(sql);

            //using (MyContext db = new MyContext())
            using(StuEntities db=new StuEntities ())
            {
                List<Students> listStudents = new List<Students>();//声明一个集合，需要传递数据到view里面去
                List<Student> listEf = db.Student.ToList();
                //List<StudentEF> listEf = db.StudentEfModel.ToList();
                //需要将 List<StudentEF>转换为视图里面的 List<Students>，循环便利listEf，将数据放进去listStudents里面
                foreach (var item in listEf)
                {
                    Students s = ToStudents(item);//将StudentEF转换Students
                    listStudents.Add(s);
                }
                return View(listStudents);
            }
            //List<Students> listStudents = TbToList(tb);//将DataTable转换为 List<Students>


        }

        //数据库模型和MVC模型中数据的转
        public Students ToStudents(Student model)
        {
            Students s = new Students();
            s.Id = model.Id;
            s.Name = model.Name;
            s.Sex = model.Sex;
            s.Mobile = model.Mobile;
            s.Email = model.Email;
            s.Age = model.Age;
            s.ClassId = model.ClassId;
            s.ClassName = model.Class.ClassName;
            return s;
        }


        [HttpGet]
        public ActionResult Add()
        {
            //string sql = "select * from Class";
            //DataTable tb = SqlHelper.MyDataTable(sql);
            //List<Classes> listClass = TbToList2(tb);
            //return View(listClass);
            //以上为SQL语句实现

            //using (MyContext db = new MyContext())
            using (StuEntities db=new StuEntities())
            {
                //List<ClassesEF> listEF = db.ClassEfModel.ToList();
                //List<Classes> listClass = new List<Classes>();
                //foreach (var item in listEF)
                //{
                //    var s = ToClasses(item);
                //    listClass.Add(s);
                //}
                //return View(listClass);


                //var p = db.ClassEfModel.ToArray();
                var p = db.Class.ToArray();
                var listClass = ToClassList(p);
                return View(listClass);


            }
        }

        [HttpGet]
        public ActionResult Update(int id)
        {
            //using (MyContext db = new MyContext())

            using (StuEntities db=new StuEntities())
            {
                //var p = db.StudentEfModel.FirstOrDefault(m => m.Id == id);
                var p = db.Student.FirstOrDefault(m=>m.Id==id);
                Students s = new Students();
                s = ToStudents(p);

                //var ClassM = db.ClassEfModel.ToArray();
                //var ClassM = db.ClassEfModel.ToArray();
                var ClassM = db.Class.ToArray();
                var listClasm = ToClassList(ClassM);

                UpdateStudent model = new UpdateStudent { studet = s, ClassesList = listClasm };
                return View(model);
            }


        }

        [HttpPost]
        public ActionResult Update(Students s)
        {
            using (StuEntities db = new StuEntities())
            {
                //StudentEF s = new StudentEF();
                var p = db.Student.FirstOrDefault(m => m.Id == s.Id);
                p.Sex = s.Sex;
                p.Name = s.Name;
                p.Age = s.Age;
                p.Mobile = s.Mobile;
                p.Email = s.Email;
                p.ClassId = s.ClassId;
                db.SaveChanges();
                return Json("ok");
                //return View("/student/list") ;
            }

        }

        
        /// <summary>
        /// ClassEF转换为Classes
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public Classes ToClasses(ClassesEF model)
        {
            Classes s = new Classes();
            s.ID = model.Id;
            s.ClassName = model.ClassName;
            return s;
        }

        public List<Classes> ToClassList(Class[] Arr)
        {
            //List<Classes> listClass = new List<Classes>();

            List<Classes> listClasses = new List<Classes>();
            if (Arr.Length > 0)
            {
                for (int i = 0; i < Arr.Length; i++)
                {
                    Classes s = new Classes();
                    s.ID = Arr[i].Id;
                    s.ClassName = Arr[i].ClassName;
                    listClasses.Add(s);
                }
            }
            return listClasses;
        }



        [HttpPost]
        public JsonResult Add(Students student)
        {
            //插入数据库
            //string str = $"insert into Student values('{student.Name}',{student.Age},'{student.Mobile}','{student.Email}',{student.ClassId},'{DateTime.Now.ToString()}')";
            //ado方式连接数据库
            //string str = "insert into Student values(@Name,@Sex,@Age,@Mobile,@Email,@ClassId,@AddTime)";
            //SqlHelper.ExecuteNonquery(str, new SqlParameter("@Name", student.Name), new SqlParameter("@Sex", student.Sex), new SqlParameter("@Age", student.Age), new SqlParameter("@Mobile", student.Mobile), new SqlParameter("@Email", student.Email), new SqlParameter("@ClassId", student.ClassId), new SqlParameter("@AddTime", DateTime.Now.ToString()));

            if (ModelState.IsValid)
            {
                Student s = new Student();
                s.AddTime = DateTime.Now;
                s.Age = student.Age;
                s.Sex = student.Sex;
                s.ClassId = student.ClassId;
                s.Email = student.Email;
                s.Mobile = student.Mobile;
                s.Name = student.Name;
                using (StuEntities db = new StuEntities())
                {
                    db.Student.Add(s);
                    db.SaveChanges();
                }
                return Json("ok");
            }
            else
            {
                return Json("no");
            }
        }

        [HttpPost]
        public JsonResult deletelll(int id)
        {
            using (StuEntities db = new StuEntities())
            {
                //StudentEF s = new StudentEF();
                var p = db.Student.FirstOrDefault(m => m.Id == id);
                db.Student.Remove(p);
                db.SaveChanges();
                return Json("ok");
            }

        }


        public List<Students> TbToList(DataTable tb)
        {
            List<Students> list = new List<Students>();
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    Students s = new Students();
                    s.Id = int.Parse(tb.Rows[i][0].ToString());
                    s.Name = tb.Rows[i][1].ToString();
                    s.Age = int.Parse(tb.Rows[i][2].ToString());
                    s.Mobile = tb.Rows[i][3].ToString();
                    s.Email = tb.Rows[i][4].ToString();
                    s.ClassId = int.Parse(tb.Rows[i][5].ToString());
                    s.ClassName = tb.Rows[i][6].ToString();
                    list.Add(s);
                }
            }
            return list;
        }
        


        public List<Classes> TbToList2(DataTable tb)
        {
            List<Classes> list = new List<Classes>();
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    Classes s = new Classes();
                    s.ID = int.Parse(tb.Rows[i][0].ToString());
                    s.ClassName = tb.Rows[i][1].ToString();
                    list.Add(s);
                }
            }
            return list;
        }
    }
}